﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;
using System.Web;

using Pub;

//using Raize.CodeSiteLogging;

namespace Pub
{
    public class AdoMSSQL
    {

        public string S_Con;

        public string DBCon;
        public string access_con;
        public SqlCommand cm = new SqlCommand();
        public SqlConnection cn = new SqlConnection();
        //public static List<SqlConnection> lst_cn = new List<SqlConnection>();
        //public static List<string> lstConKey = new List<string>();
        //public static List<string> lstConVal = new List<string>();

        public SqlDataAdapter da;
        public DataSet ds;
        public int ds_count = 0;
        public SqlParameter[] pars;
        public List<SqlParameter> pars2;
        public string sqlServer_con;
        public string s_sql = "";

        public const int I_ADD = 1;
        public const int I_DEL = 2;
        public const int I_EDT = 3;
        public const int I_FREE = 4;


        public AdoMSSQL()
        {
            OpenDB();
        }

        public AdoMSSQL(string skey)
        {
            OpenDB(skey);
        }

        public AdoMSSQL(string sServer, string sUser, string sPass, string sDataBase)
        {
            OpenDB(sServer, sUser, sPass, sDataBase);
        }

        public Boolean OpenDB()
        {
            return OpenDB("DBCon");
        }

        public Boolean OpenDB(string skey)
        {
            try
            {
                this.DBCon = skey;
                S_Con = CConfig.getPermite(DBCon);

                cn = new SqlConnection(S_Con);
                return true;
                /*
                //i_ID = lstConKey.IndexOf(DBCon);

                if (i_ID == -1)
                {
                    S_Con = CConfig.getPermite(DBCon);

                    //lstConKey.Add(DBCon);
                    //lstConVal.Add(S_Con);

                    SqlConnection cn = new SqlConnection(S_Con);
                    //cn.Open();
                    //lst_cn.Add(cn);
                    //i_ID = lst_cn.Count - 1;

                    ////CDebug.Send(">>>>>>>>>>>>>>>>>>>创建数据库连接" + lst_cn.Count + ": " + S_Con);
                    return true;
                }
                else
                {

                    //if (lst_cn[i_ID].State != System.Data.ConnectionState.Open)
                    //    lst_cn[i_ID].Open();
                    return true;

                }
                 * */
            }
            catch (Exception ex)
            {
                //CDebug.SendError("AdoMSSQL.cs OpenDB:", ex);
                return false;
            }
        }


        public Boolean OpenDB(string sServer, string sUser, string sPass, string sDataBase)
        {
            try
            {

                S_Con = "Data Source=" + sServer + ";Initial Catalog=" + sDataBase + ";Persist Security Info=True;User ID=" + sUser + ";Password=" + sPass + ";";

                cn = new SqlConnection(S_Con);
                return true;
                /*
                //i_ID = lstConKey.IndexOf(DBCon);

                if (i_ID == -1)
                {
                    S_Con = CConfig.getPermite(DBCon);

                    //lstConKey.Add(DBCon);
                    //lstConVal.Add(S_Con);

                    SqlConnection cn = new SqlConnection(S_Con);
                    //cn.Open();
                    //lst_cn.Add(cn);
                    //i_ID = lst_cn.Count - 1;

                    ////CDebug.Send(">>>>>>>>>>>>>>>>>>>创建数据库连接" + lst_cn.Count + ": " + S_Con);
                    return true;
                }
                else
                {

                    //if (lst_cn[i_ID].State != System.Data.ConnectionState.Open)
                    //    lst_cn[i_ID].Open();
                    return true;

                }
                 * */
            }
            catch (Exception ex)
            {
                //CDebug.SendError("AdoMSSQL.cs OpenDB:", ex);
                return false;
            }
        }

        public Boolean CloseDB()
        {
            try
            {
                //lst_cn[i_ID].Close();
                if (cn.State != ConnectionState.Closed) cn.Close();
                return true;
            }
            catch (Exception ex)
            {
                //CDebug.Send("AdoMSSQL.cs CloseDB:", ex);
                return false;
            }
        }

        /// <summary>
        /// 执行非select语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd1(string sql)
        {
            cn.Open();
            cm.Connection = cn;

            cm = new SqlCommand(sql, cn);

            int i = cm.ExecuteNonQuery();

            cn.Close();
            return i;

        }

        public int cmd1(string sql, List<SqlParameter> pars)
        {
            if (pars == null) return 0;
            SqlParameter[] pars2 = new SqlParameter[pars.Count];
            for (int i = 0; i < pars.Count; i++) pars2[i] = pars[i];

            cn.Open();
            cm.Connection = cn;
            cm.CommandText = sql;
            cm.Parameters.Clear();
            cm.Parameters.AddRange(pars2);
            cm.CommandType = CommandType.StoredProcedure;
            cn.Close();
            return cm.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd2(string sql)
        {
            if (pars == null) return 0;

            cn.Open();
            cm.Connection = cn;

            cm.CommandText = sql;
            cm.Parameters.Clear();
            cm.Parameters.AddRange(pars);
            cm.CommandType = CommandType.StoredProcedure;
            cn.Close();

            return cm.ExecuteNonQuery();
        }

        public int cmd2(string sql, SqlParameter[] pars)
        {
            try
            {
                if (pars == null) return 0;

                cn.Open();
                cm.Connection = cn;

                cm.CommandText = sql;
                cm.Parameters.Clear();
                cm.Parameters.AddRange(pars);
                cm.CommandType = CommandType.StoredProcedure;
                cn.Close();

                return cm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                //CDebug.SendError(ex.ToString());
                return 0;
            }
        }

        public Int32 getNewID(string sTab, string sCol)
        {
            string sql = "select Isnull(Max(Convert(int," + sCol + ")),0) + 1 as s1 from " + sTab;
            cn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();
            if (CDB.dsIsNull(ds)) return 0;
            else return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public Image read_img(string sql, string col)
        {
            //string sql = "select * from " + tab + " where id=" + id;
            DataSet ds = sel1(sql);
            if (ds_count == 0)
                return null;
            try
            {
                Byte[] Bytes = (Byte[])ds.Tables[0].Rows[0][col];
                MemoryStream mStream = new MemoryStream();
                mStream.Write(Bytes, 0, Bytes.Length);
                Image Img = Image.FromStream(mStream);
                return Img;
            }
            catch (System.Exception ex)
            {
                //CDebug.Send("AdoMSSQL.cs read_img:", ex);
                return null;
            }
        }

        public void save_img(string tab, string par1, string col, Image img)
        {
            MemoryStream stream = new MemoryStream();
            img.Save(stream, ImageFormat.Png);
            Byte[] bytes = stream.ToArray();
            string sql = "update " + tab + " set " + col + " = @bytes " + par1;
            cn.Open();
            cm = new SqlCommand(sql, cn);
            cm.Parameters.Add("@bytes", SqlDbType.Image).Value = bytes;
            cm.ExecuteNonQuery();
            cn.Close();
        }

        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet sel1(string sql)
        {
            try
            {
                this.s_sql = sql;
                cn.Open();
                da = new SqlDataAdapter(sql, cn);
                ds = new DataSet();

                da.Fill(ds);
                SqlCommandBuilder sqlCb = new SqlCommandBuilder(da);

                cn.Close();

                //da.FillSchema(ds, SchemaType.Mapped);
                try
                {
                    if (ds == null || ds.Tables[0].Rows.Count == 0)
                    {
                        ds_count = 0;
                    }

                    ds_count = ds.Tables[0].Rows.Count;
                }
                catch (System.Exception ex1)
                {
                    ds_count = 0;
                    //CDebug.Send("返回结果个数出错", ex1);
                }

                return ds;

            }
            catch (Exception ex)
            {
                if (cn.State != System.Data.ConnectionState.Closed) cn.Close();

                //CDebug.Send(sql + "; " + ex.ToString());
                //CDebug.Send("AdoMSSQL.cs sel1:", ex);
                ds_count = 0;
                throw (ex);
                //return null;
            }
        }

        public void setParsCount(int i_count)
        {
            pars = new SqlParameter[i_count];
            for (int i = 0; i < i_count; i++)
            {
                pars[i] = new SqlParameter();
            }
        }

        public Boolean update(string strSQL, DataSet ds1, int i_typ)
        {
            try
            {

                cn.Open();
                da = new SqlDataAdapter(strSQL, cn);
                ds = new DataSet();

                da.Fill(ds);
                SqlCommandBuilder sqlCb = new SqlCommandBuilder(da);
                ////CDebug.Send(ds);
                ////CDebug.Send("" + ds.Tables[0].PrimaryKey.Length);

                Boolean b_has = false;
                string sKeyName = ds.Tables[0].Columns[0].Caption;
                switch (i_typ)
                {
                    case I_ADD:

                        foreach (DataRow dr1 in ds1.Tables[0].Rows)
                        {
                            b_has = false;
                            foreach (DataRow dr2 in ds.Tables[0].Rows)
                            {
                                if (dr1[sKeyName] == dr2[sKeyName])
                                {
                                    b_has = true;
                                    continue;
                                }
                            }
                            if (b_has == false)
                            {
                                DataRow dr3 = ds.Tables[0].NewRow();
                                for (int i = 0; i < dr1.ItemArray.Length; i++)
                                {
                                    dr3[i] = dr1[i];
                                }
                            }
                        }
                        break;

                    case I_DEL:

                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow dr1 = ds.Tables[0].Rows[i];
                            b_has = false;
                            foreach (DataRow dr2 in ds1.Tables[0].Rows)
                            {
                                if (dr1[sKeyName] == dr2[sKeyName])
                                {
                                    b_has = true;
                                    continue;
                                }
                            }
                            if (b_has == false)
                            {
                                ds.Tables[0].Rows[i].Delete();
                            }
                        }
                        break;

                    case I_EDT:

                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                            {
                                ds.Tables[0].Rows[i][j] = ds1.Tables[0].Rows[i][j];
                            }
                        }

                        break;
                    case I_FREE:
                        //del
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow dr1 = ds.Tables[0].Rows[i];
                            b_has = false;
                            foreach (DataRow dr2 in ds1.Tables[0].Rows)
                            {
                                if (dr1[sKeyName] == dr2[sKeyName])
                                {
                                    b_has = true;
                                    continue;
                                }
                            }
                            if (b_has == false)
                            {
                                ds.Tables[0].Rows[i].Delete();
                            }
                        }

                        //add
                        foreach (DataRow dr1 in ds1.Tables[0].Rows)
                        {
                            b_has = false;
                            foreach (DataRow dr2 in ds.Tables[0].Rows)
                            {
                                if (dr1[sKeyName] == dr2[sKeyName])
                                {
                                    b_has = true;
                                    continue;
                                }
                            }
                            if (b_has == false)
                            {
                                DataRow dr3 = ds.Tables[0].NewRow();
                                for (int i = 0; i < dr1.ItemArray.Length; i++)
                                {
                                    dr3[i] = dr1[i];
                                }
                            }
                        }

                        //edt
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                            {
                                ds.Tables[0].Rows[i][j] = ds1.Tables[0].Rows[i][j];
                            }
                        }
                        break;
                }

                da.Update(ds);
                cn.Close();

                return true;
            }
            catch (System.Exception ex)
            {
                if (cn.State != System.Data.ConnectionState.Closed) cn.Close();

                //CDebug.SendError(ex.ToString());
                //CDebug.Send("更新数据是发生错误", ex);
                return false;
            }
        }

        public Boolean update(int i_typ)
        {
            string strSQL = this.s_sql;
            DataSet ds1 = new DataSet();
            ds1 = this.ds;

            return update(strSQL, ds1, i_typ);
        }

        public Boolean update()
        {
            string strSQL = this.s_sql;
            DataSet ds1 = new DataSet();
            ds1 = this.ds;

            return update(strSQL, ds1, I_EDT);
        }

    }
}